iLoADER

HOME

Updating non-null fields only

You can perform an update in iLoader when each record in the update file contains an unspecified mix of blank and valid data.

The value in the loaded table should only be changed if the field is not blank

Consider the following update file:

File Name = CustomerUpdateData.txt (Record Length = 8, Comma Delimited)

1234, Jones, , , , , M, 1253, Smith, , , , , , 100000 2445, , 14 Church Street, Redland, BS6 56K, , , 3566, , , , , , , 4566, , , , , 0890 824822,

Data is not supplied for each field of each record. The Engine table should only be updated when data is specified - otherwise it should remain unchanged.

A simple Update Table definition as follows will replace the existing data with Nulls if no data is encountered in the update file:

BEGIN TABLE_DECLARATION

format = DELIMITED delimiter = COMMA qualifier = NONE scriptfile = CUSTOMER_UPDATE.TXT datfile = CustomerUpdateData1.txt type = UPDATE width = 8 date = 15012004

END TABLE_DECLARATION

CustomerUpdateData.txt

TABLE=[test].[Customer] key=FID BEGIN TABLE_DEFINITION

FID , TEXT , 0 , 8 Surname , TEXT , 1 , 40 Address1 , TEXT , 2 , 40 Address2 , TEXT , 3 , 40 Postcode , TEXT , 4 , 10 TelNo , TEXT , 5 , 12 Gender , TEXT , 6 , 10 Income , REAL ,7, 15

END TABLE_DEFINITION

Updates work by matching on Column name. If the column name as specified in the TABLE_DEFINITION doesn't exist in the table then a new column will be created.

This can be used to prevent us overwriting data that we want to keep.

The approach we will take is as follows:

  1. Load all data into temporary columns
  2. Create a new column that takes the value from the temporary column if it is Non-Null, otherwise, take the value from the original column.
  3. Delete the original column and the temporary column and rename the new column to be the same as the original.

The script files will look as follows:

Control.txt

BEGIN TABLE_DECLARATION

format = DELIMITED delimiter = COMMA qualifier = NONE scriptfile = CUSTOMER_UPDATE.TXT datfile = CustomerUpdateData1.txt type = UPDATE width = 8 date = 15012004

END TABLE_DECLARATION

Customer_Update.txt

The file contains NEW column names for each column that will be updated.

TABLE=[test].[Customer] KEY = FID

BEGIN TABLE_DEFINITION

FID , TEXT , 0 , 8 SurnameTEMP , TEXT , 1 , 40 Address1TEMP , TEXT , 2 , 40 Address2TEMP , TEXT , 3 , 40 PostcodeTEMP , TEXT , 4 , 10 TelNoTEMP , TEXT , 5 , 12 GenderTEMP , TEXT , 6 , 10 IncomeTEMP , REAL ,7, 15

END TABLE_DEFINITION

Metadata.txt

REM // Create New Expressions

EXPRESSION

[Test].[Customer] , SurnameNEW , {If(SurnameTEMP=NULL,Surname, SurnameTEMP)}

EXPRESSION

[Test].[Customer] , Address1NEW , {If(Address1TEMP=NULL,Address1, Address1TEMP)}

EXPRESSION

[Test].[Customer] , Address2NEW , {If(Address2TEMP=NULL,Address2, Address2TEMP)}

EXPRESSION

[Test].[Customer] , PostcodeNEW , {If(PostcodeTEMP=NULL,Postcode, PostcodeTEMP)}

EXPRESSION

[Test].[Customer] , TelNoNEW , {If(SurnameTEMP=NULL,TelNo, TelNoTEMP)}

EXPRESSION

[Test].[Customer] , GenderNEW , {If(GenderTEMP=NULL,Gender, GenderTEMP)}

EXPRESSION

[Test].[Customer] , IncomeNEW , {If(SurnameTEMP=NULL,Income, IncomeTEMP)}

REM // Replace existing field with new field

DROP [Test].[Customer].[SurnameTEMP]

DROP [Test].[Customer].[Surname]

RENAME [Test].[Customer].[SurnameNEW] , [Test].[Customer].[Surname]

DROP [Test].[Customer].[Address1TEMP]

DROP [Test].[Customer].[Address1]

RENAME [Test].[Customer].[Address1NEW] , [Test].[Customer].[Address1]

DROP [Test].[Customer].[Address2TEMP]

DROP [Test].[Customer].[Address2]

RENAME [Test].[Customer].[Address2NEW] , [Test].[Customer].[Address2]

DROP [Test].[Customer].[PostCodeTEMP]

DROP [Test].[Customer].[PostCode]

RENAME [Test].[Customer].[PostcodeNEW] , [Test].[Customer].[Postcode]

DROP [Test].[Customer].[TelNoTEMP]

DROP [Test].[Customer].[TelNo]

RENAME [Test].[Customer].[TelNoNEW] , [Test].[Customer].[TelNo]

DROP [Test].[Customer].[GenderTEMP]

DROP [Test].[Customer].[Gender]

RENAME [Test].[Customer].[GenderNEW] , [Test].[Customer].[Gender]

DROP [Test].[Customer].[IncomeTEMP]

DROP [Test].[Customer].[Income]

RENAME [Test].[Customer].[IncomeNEW] , [Test].[Customer].[Income]

  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice